package com.alimentamais.app.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


public class DBConexao {
	
	    private static final String URL_MYSQL = "jdbc:mysql://localhost/alimentamais";
	    private static final String DRIVER_CLASS_MYSQL = "com.mysql.jdbc.Driver";
	    private static final String USER = "root";
	    private static final String PASS = "admin2014";
	 
	    public static Connection getConnection() {
	        System.out.println("Conectando ao Banco de Dados");
	        try {
	            Class.forName(DRIVER_CLASS_MYSQL);
	            return DriverManager.getConnection(URL_MYSQL, USER, PASS);
	        } catch (ClassNotFoundException e) {
	            e.printStackTrace();
	        } catch (SQLException e) {
	            throw new RuntimeException(e);
	        }
	        return null;
	    }
	 
	    public static void close(Connection conn, PreparedStatement stm, ResultSet rs) {
	        try {
	            if (conn!= null) {
	                conn.close();
	            }
	 
	            if (stm!= null) {
	                stm.close();
	            }
	 
	            if (rs!= null) {
	                rs.close();
	            }
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	    }
	 
	    public static void createTable() {
	        Connection connection = getConnection();
	        PreparedStatement stm = null;
	        PreparedStatement stm1 = null;
	        PreparedStatement stm2 = null;
	        PreparedStatement stm3 = null;
	        
	        String sql = "CREATE TABLE IF NOT EXISTS usuario (\n" +
	                "  IdUsuario INT NOT NULL AUTO_INCREMENT,\n" +
	                "  Nome VARCHAR(45) NOT NULL,\n" +
	                "  Senha VARCHAR(16) NOT NULL,\n" +
	                "  Idade INT NOT NULL,\n" +
	                "  Peso DOUBLE NOT NULL,\n" +
	                "  Altura DOUBLE NOT NULL,\n" +
	                "  Sexo VARCHAR(10) NOT NULL,\n" +
	                "  AtivFisica VARCHAR(30) NOT NULL,\n" +
	                "  Calorias VARCHAR(10) NOT NULL,\n"+
	                "  Data TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,\n"+
	                "  PRIMARY KEY (IdUsuario)\n" +
	                ")";
	      
	        String sql1="CREATE TABLE IF NOT EXISTS ingrediente (\n" +
	                "  IdIngrediente INT NOT NULL AUTO_INCREMENT,\n" +
	                "  Nome VARCHAR(45) NOT NULL,\n" +
	                "  Descricao TEXT NULL,\n" +
	                "  Peso DOUBLE NOT NULL,\n" +
	                "  Quantidade DOUBLE NOT NULL,\n" +
	                "  Proteinas DOUBLE NOT NULL,\n" +
	                "  Carboidratos DOUBLE NOT NULL,\n" +
	                "  Gorduras DOUBLE NOT NULL,\n" +
	                "  Calorias DOUBLE NOT NULL,\n" +
	                "  Categoria VARCHAR(20) NOT NULL,\n" +
	                "  Data TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,\n"+
	                "  PRIMARY KEY (IdIngrediente)\n" +
	                ")";
	        
	        String sql2="CREATE TABLE IF NOT EXISTS receita (\n" +
	                "  IdReceita INT NOT NULL AUTO_INCREMENT,\n" +
	                "  Nome VARCHAR(45) NOT NULL,\n" +
	                "  Descricao TEXT NULL,\n" +
	                "  TipoReceita INT NOT NULL,\n" +
	                "  Categoria VARCHAR(20) NOT NULL,\n" +
	                "  Data TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,\n"+
	                "  PRIMARY KEY (IdReceita)\n" +
	                ")";
	        
	        String sql3="CREATE TABLE IF NOT EXISTS ingredienteReceita (\n" +
	                "  IdIngredienteReceita INT NOT NULL AUTO_INCREMENT,\n" +
	                "  receita_IdReceita INT NOT NULL,\n" +
	                "  ingrediente_IdIngrediente INT NOT NULL,\n" +
	                "  Data TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,\n"+
	                "  PRIMARY KEY (IdIngredienteReceita),\n" +
	                "  FOREIGN KEY (receita_IdReceita) REFERENCES receita(IdReceita),\n"+
	                "  FOREIGN KEY (ingrediente_IdIngrediente) REFERENCES ingrediente(IdIngrediente)\n"+
	                ")";
	        
	        try {
	            stm = connection.prepareStatement(sql);
	            stm.execute();
	            
	            stm1 = connection.prepareStatement(sql1);
	            stm1.execute();
	            
	            stm2 = connection.prepareStatement(sql2);
	            stm2.execute();
	            
	            stm3 = connection.prepareStatement(sql3);
	            stm3.execute();
	            
	            System.out.println("Tabelas Criadas Ok!");
	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            close(connection, stm, null);
	        }
	    }
	}

